﻿// MIT License
// 开源地址：https://gitee.com/co1024/AbcMvc
// Copyright (c) 2021-2023 1024
// Abc.Mvc=Furion+EF+SqlSugar+Pear layui admin.

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;

namespace Abc.Utils;

public static class ExcelHelper
{
    /*
      XLS  转 XLSX 参考：https://www.cnblogs.com/HelloQLQ/p/16166140.html
     */

    /// <summary>
    /// XLS  转 XLSX
    /// </summary>
    /// <param name="xlsPath">xls 文件路径</param>
    /// <returns></returns>
    public static string ConvertXlsToXlsx(string xlsPath)
    {
        using var oldWorkbook = new HSSFWorkbook(new FileStream(xlsPath, FileMode.Open));

        var newExcelPath = xlsPath.ToLower().Replace(".xls", ".xlsx");
        using var fileStream = new FileStream(newExcelPath, FileMode.Create);
        {
            var newWorkBook = new XSSFWorkbook();
            //遍历所有的sheets
            for (int oldWorkBookIndex = 0; oldWorkBookIndex < oldWorkbook.NumberOfSheets; oldWorkBookIndex++)
            {
                var oldWorkSheet = oldWorkbook.GetSheetAt(oldWorkBookIndex);

                var newWorkSheet = newWorkBook.CreateSheet("Sheet1");

                foreach (HSSFRow oldRow in oldWorkSheet)
                {
                    var newRow = newWorkSheet.CreateRow(oldRow.RowNum);

                    for (int ii = oldRow.FirstCellNum; ii < oldRow.LastCellNum; ii++)
                    {
                        var newCell = newRow.CreateCell(ii);
                        newCell = oldRow.Cells[ii];
                    }
                }

                //合并单元格
                int sheetMergerCount = oldWorkSheet.NumMergedRegions;
                for (int me = 0; me < sheetMergerCount; me++)
                    newWorkSheet.AddMergedRegion(oldWorkSheet.GetMergedRegion(me));
            }

            newWorkBook.Write(fileStream);
            newWorkBook?.Close();
        }
        oldWorkbook?.Close();

        return newExcelPath;
    }

    //public string ConvertXlsToXlsx(string xlsPath, string newExcelPath)
    //{
    //    var oldWorkbook = new HSSFWorkbook(new FileStream(xlsPath, FileMode.Open));
    //    var oldWorkSheet = oldWorkbook.GetSheetAt(0);
    //    int m = 0;

    //    try
    //    {
    //        using (var fileStream = new FileStream(newExcelPath, FileMode.Create))
    //        {
    //            var newWorkBook = new XSSFWorkbook();
    //            var newWorkSheet = newWorkBook.CreateSheet("Sheet1");
    //            int i = 0;
    //            foreach (HSSFRow oldRow in oldWorkSheet)
    //            {
    //                var newRow = newWorkSheet.CreateRow(oldRow.RowNum);

    //                for (int ii = oldRow.FirstCellNum; ii < oldRow.Cells.Count; ii++)
    //                {
    //                    m = ii;
    //                    var newCell = newRow.CreateCell(ii);
    //                    newCell.SetCellValue(GetValueType(oldRow.Cells[ii]).ToString());
    //                }

    //            }

    //            int sheetMergerCount = oldWorkSheet.NumMergedRegions;
    //            for (int me = 0; me < sheetMergerCount; me++)
    //                newWorkSheet.AddMergedRegion(oldWorkSheet.GetMergedRegion(me));

    //            newWorkBook.Write(fileStream);
    //            newWorkBook.Close();
    //        }
    //    }
    //    catch (Exception ex)
    //    {
    //        int b = m;
    //        throw;
    //    }
    //    oldWorkbook.Close();

    //    return newExcelPath;
    //}

    #region Xls转Xlsx

    //public static void ConvertXlsToXlsx2(string newExcelPath)
    //{
    //    var oldWorkbook = new HSSFWorkbook(new FileStream(newExcelPath, FileMode.Open));
    //    var oldWorkSheet = oldWorkbook.GetSheetAt(0);
    //    using (var fileStream = new FileStream(newExcelPath, FileMode.Create))
    //    {
    //        var newWorkBook1 = new XSSFWorkbook();
    //        var sheet = oldWorkSheet.CrossCloneSheet(newWorkBook1, "Sheet1");
    //        newWorkBook1.Add(sheet);
    //        newWorkBook1.Write(fileStream);
    //        newWorkBook1.Close();

    //    }
    //    oldWorkbook.Close();
    //}

    ///// <summary>
    ///// 跨工作薄Workbook复制工作表Sheet
    ///// </summary>
    ///// <param name="sSheet">源工作表Sheet</param>
    ///// <param name="dWb">目标工作薄Workbook</param>
    ///// <param name="dSheetName">目标工作表Sheet名</param>
    ///// <param name="clonePrintSetup">是否复制打印设置</param>
    //public static ISheet CrossCloneSheet(this ISheet sSheet, IWorkbook dWb, string dSheetName, bool clonePrintSetup)
    //{
    //    ISheet dSheet;
    //    dSheetName = string.IsNullOrEmpty(dSheetName) ? sSheet.SheetName : dSheetName;
    //    dSheetName = (dWb.GetSheet(dSheetName) == null) ? dSheetName : dSheetName + "_拷贝";
    //    dSheet = dWb.GetSheet(dSheetName) ?? dWb.CreateSheet(dSheetName);
    //    CopySheet(sSheet, dSheet);
    //    if (clonePrintSetup)
    //        ClonePrintSetup(sSheet, dSheet);
    //    dWb.SetActiveSheet(dWb.GetSheetIndex(dSheet));  //当前Sheet作为下次打开默认Sheet
    //    return dSheet;
    //}
    ///// <summary>
    ///// 跨工作薄Workbook复制工作表Sheet
    ///// </summary>
    ///// <param name="sSheet">源工作表Sheet</param>
    ///// <param name="dWb">目标工作薄Workbook</param>
    ///// <param name="dSheetName">目标工作表Sheet名</param>
    //public static ISheet CrossCloneSheet(this ISheet sSheet, IWorkbook dWb, string dSheetName)
    //{
    //    bool clonePrintSetup = true;
    //    return CrossCloneSheet(sSheet, dWb, dSheetName, clonePrintSetup);
    //}
    ///// <summary>
    ///// 跨工作薄Workbook复制工作表Sheet
    ///// </summary>
    ///// <param name="sSheet">源工作表Sheet</param>
    ///// <param name="dWb">目标工作薄Workbook</param>
    //public static ISheet CrossCloneSheet(this ISheet sSheet, IWorkbook dWb)
    //{
    //    string dSheetName = sSheet.SheetName;
    //    bool clonePrintSetup = true;
    //    return CrossCloneSheet(sSheet, dWb, dSheetName, clonePrintSetup);
    //}

    //private static IFont FindFont(this IWorkbook dWb, IFont font, List<IFont> dFonts)
    //{
    //    //IFont dFont = dWb.FindFont(font.Boldweight, font.Color, (short)font.FontHeight, font.FontName, font.IsItalic, font.IsStrikeout, font.TypeOffset, font.Underline);
    //    IFont dFont = null;
    //    foreach (IFont currFont in dFonts)
    //    {
    //        //if (currFont.Charset != font.Charset) continue;
    //        //else
    //        //if (currFont.Color != font.Color) continue;
    //        //else
    //        if (currFont.FontName != font.FontName) continue;
    //        else if (currFont.FontHeight != font.FontHeight) continue;
    //        else if (currFont.IsBold != font.IsBold) continue;
    //        else if (currFont.IsItalic != font.IsItalic) continue;
    //        else if (currFont.IsStrikeout != font.IsStrikeout) continue;
    //        else if (currFont.Underline != font.Underline) continue;
    //        else if (currFont.TypeOffset != font.TypeOffset) continue;
    //        else { dFont = currFont; break; }
    //    }
    //    return dFont;
    //}
    //private static ICellStyle FindStyle(this IWorkbook dWb, IWorkbook sWb, ICellStyle style, List<ICellStyle> dCellStyles, List<IFont> dFonts)
    //{
    //    ICellStyle dStyle = null;
    //    foreach (ICellStyle currStyle in dCellStyles)
    //    {
    //        if (currStyle.Alignment != style.Alignment) continue;
    //        else if (currStyle.VerticalAlignment != style.VerticalAlignment) continue;
    //        else if (currStyle.BorderTop != style.BorderTop) continue;
    //        else if (currStyle.BorderBottom != style.BorderBottom) continue;
    //        else if (currStyle.BorderLeft != style.BorderLeft) continue;
    //        else if (currStyle.BorderRight != style.BorderRight) continue;
    //        else if (currStyle.TopBorderColor != style.TopBorderColor) continue;
    //        else if (currStyle.BottomBorderColor != style.BottomBorderColor) continue;
    //        else if (currStyle.LeftBorderColor != style.LeftBorderColor) continue;
    //        else if (currStyle.RightBorderColor != style.RightBorderColor) continue;
    //        //else if (currStyle.BorderDiagonal != style.BorderDiagonal) continue;
    //        //else if (currStyle.BorderDiagonalColor != style.BorderDiagonalColor) continue;
    //        //else if (currStyle.BorderDiagonalLineStyle != style.BorderDiagonalLineStyle) continue;
    //        //else if (currStyle.FillBackgroundColor != style.FillBackgroundColor) continue;
    //        //else if (currStyle.FillBackgroundColorColor != style.FillBackgroundColorColor) continue;
    //        //else if (currStyle.FillForegroundColor != style.FillForegroundColor) continue;
    //        //else if (currStyle.FillForegroundColorColor != style.FillForegroundColorColor) continue;
    //        //else if (currStyle.FillPattern != style.FillPattern) continue;
    //        else if (currStyle.Indention != style.Indention) continue;
    //        else if (currStyle.IsHidden != style.IsHidden) continue;
    //        else if (currStyle.IsLocked != style.IsLocked) continue;
    //        else if (currStyle.Rotation != style.Rotation) continue;
    //        else if (currStyle.ShrinkToFit != style.ShrinkToFit) continue;
    //        else if (currStyle.WrapText != style.WrapText) continue;
    //        else if (!currStyle.GetDataFormatString().Equals(style.GetDataFormatString())) continue;
    //        else
    //        {
    //            IFont sFont = sWb.GetFontAt(style.FontIndex);
    //            IFont dFont = dWb.FindFont(sFont, dFonts);
    //            if (dFont == null) continue;
    //            else
    //            {
    //                currStyle.SetFont(dFont);
    //                dStyle = currStyle;
    //                break;
    //            }
    //        }
    //    }
    //    return dStyle;
    //}
    //private static IFont CopyFont(this IFont dFont, IFont sFont, List<IFont> dFonts)
    //{
    //    //dFont.Charset = sFont.Charset;
    //    //dFont.Color = sFont.Color;
    //    dFont.FontHeight = sFont.FontHeight;
    //    dFont.FontName = sFont.FontName;
    //    dFont.IsBold = sFont.IsBold;
    //    dFont.IsItalic = sFont.IsItalic;
    //    dFont.IsStrikeout = sFont.IsStrikeout;
    //    dFont.Underline = sFont.Underline;
    //    dFont.TypeOffset = sFont.TypeOffset;
    //    dFonts.Add(dFont);
    //    return dFont;
    //}
    //private static ICellStyle CopyStyle(this ICellStyle dCellStyle, ICellStyle sCellStyle, IWorkbook dWb, IWorkbook sWb, List<ICellStyle> dCellStyles, List<IFont> dFonts)
    //{
    //    ICellStyle currCellStyle = dCellStyle;
    //    currCellStyle.Alignment = sCellStyle.Alignment;
    //    currCellStyle.VerticalAlignment = sCellStyle.VerticalAlignment;
    //    currCellStyle.BorderTop = sCellStyle.BorderTop;
    //    currCellStyle.BorderBottom = sCellStyle.BorderBottom;
    //    currCellStyle.BorderLeft = sCellStyle.BorderLeft;
    //    currCellStyle.BorderRight = sCellStyle.BorderRight;
    //    currCellStyle.TopBorderColor = sCellStyle.TopBorderColor;
    //    currCellStyle.LeftBorderColor = sCellStyle.LeftBorderColor;
    //    currCellStyle.RightBorderColor = sCellStyle.RightBorderColor;
    //    currCellStyle.BottomBorderColor = sCellStyle.BottomBorderColor;
    //    //dCellStyle.BorderDiagonal = sCellStyle.BorderDiagonal;
    //    //dCellStyle.BorderDiagonalColor = sCellStyle.BorderDiagonalColor;
    //    //dCellStyle.BorderDiagonalLineStyle = sCellStyle.BorderDiagonalLineStyle;
    //    //dCellStyle.FillBackgroundColor = sCellStyle.FillBackgroundColor;
    //    dCellStyle.FillForegroundColor = sCellStyle.FillForegroundColor;
    //    //dCellStyle.FillPattern = sCellStyle.FillPattern;
    //    currCellStyle.Indention = sCellStyle.Indention;
    //    currCellStyle.IsHidden = sCellStyle.IsHidden;
    //    currCellStyle.IsLocked = sCellStyle.IsLocked;
    //    currCellStyle.Rotation = sCellStyle.Rotation;
    //    currCellStyle.ShrinkToFit = sCellStyle.ShrinkToFit;
    //    currCellStyle.WrapText = sCellStyle.WrapText;
    //    currCellStyle.DataFormat = dWb.CreateDataFormat().GetFormat(sWb.CreateDataFormat().GetFormat(sCellStyle.DataFormat));
    //    IFont sFont = sCellStyle.GetFont(sWb);
    //    IFont dFont = dWb.FindFont(sFont, dFonts) ?? dWb.CreateFont().CopyFont(sFont, dFonts);
    //    currCellStyle.SetFont(dFont);
    //    dCellStyles.Add(currCellStyle);
    //    return currCellStyle;
    //}

    //private static void CopySheet(ISheet sSheet, ISheet dSheet)
    //{
    //    var maxColumnNum = 0;
    //    List<ICellStyle> dCellStyles = new List<ICellStyle>();
    //    List<IFont> dFonts = new List<IFont>();
    //    MergerRegion(sSheet, dSheet);
    //    for (int i = sSheet.FirstRowNum; i <= sSheet.LastRowNum; i++)
    //    {
    //        IRow sRow = sSheet.GetRow(i);
    //        IRow dRow = dSheet.CreateRow(i);
    //        if (sRow != null)
    //        {
    //            CopyRow(sRow, dRow, dCellStyles, dFonts);
    //            if (sRow.LastCellNum > maxColumnNum)
    //                maxColumnNum = sRow.LastCellNum;
    //        }
    //    }
    //    for (int i = 0; i <= maxColumnNum; i++)
    //        dSheet.SetColumnWidth(i, sSheet.GetColumnWidth(i));
    //}
    //private static void CopyRow(IRow sRow, IRow dRow, List<ICellStyle> dCellStyles, List<IFont> dFonts)
    //{
    //    dRow.Height = sRow.Height;
    //    ISheet sSheet = sRow.Sheet;
    //    ISheet dSheet = dRow.Sheet;
    //    for (int j = sRow.FirstCellNum; j <= sRow.LastCellNum; j++)
    //    {
    //        NPOI.SS.UserModel.ICell sCell = sRow.GetCell(j);
    //        NPOI.SS.UserModel.ICell dCell = dRow.GetCell(j);
    //        if (sCell != null)
    //        {
    //            if (dCell == null)
    //                dCell = dRow.CreateCell(j);
    //            CopyCell(sCell, dCell, dCellStyles, dFonts);
    //        }
    //    }
    //}
    //private static void CopyCell(NPOI.SS.UserModel.ICell sCell, NPOI.SS.UserModel.ICell dCell, List<ICellStyle> dCellStyles, List<IFont> dFonts)
    //{
    //    ICellStyle currCellStyle = dCell.Sheet.Workbook.FindStyle(sCell.Sheet.Workbook, sCell.CellStyle, dCellStyles, dFonts);
    //    if (currCellStyle == null)
    //        currCellStyle = dCell.Sheet.Workbook.CreateCellStyle().CopyStyle(sCell.CellStyle, dCell.Sheet.Workbook, sCell.Sheet.Workbook, dCellStyles, dFonts);
    //    dCell.CellStyle = currCellStyle;
    //    switch (sCell.CellType)
    //    {
    //        case CellType.String:
    //            dCell.SetCellValue(sCell.StringCellValue);
    //            break;
    //        case CellType.Numeric:
    //            dCell.SetCellValue(sCell.NumericCellValue);
    //            break;
    //        case CellType.Blank:
    //            dCell.SetCellType(CellType.Blank);
    //            break;
    //        case CellType.Boolean:
    //            dCell.SetCellValue(sCell.BooleanCellValue);
    //            break;
    //        case CellType.Error:
    //            dCell.SetCellValue(sCell.ErrorCellValue);
    //            break;
    //        case CellType.Formula:
    //            dCell.SetCellFormula(sCell.CellFormula);
    //            break;
    //        default:
    //            break;
    //    }
    //}

    //private static void MergerRegion(ISheet sSheet, ISheet dSheet)
    //{
    //    int sheetMergerCount = sSheet.NumMergedRegions;
    //    for (int i = 0; i < sheetMergerCount; i++)
    //        dSheet.AddMergedRegion(sSheet.GetMergedRegion(i));
    //}
    //private static void ClonePrintSetup(ISheet sSheet, ISheet dSheet)
    //{
    //    //工作表Sheet页面打印设置
    //    dSheet.PrintSetup.Copies = 1;                               //打印份数
    //    dSheet.PrintSetup.PaperSize = sSheet.PrintSetup.PaperSize;  //纸张大小
    //    dSheet.PrintSetup.Landscape = sSheet.PrintSetup.Landscape;  //纸张方向：默认纵向false(横向true)
    //    dSheet.PrintSetup.Scale = sSheet.PrintSetup.Scale;          //缩放方式比例
    //    dSheet.PrintSetup.FitHeight = sSheet.PrintSetup.FitHeight;  //调整方式页高
    //    dSheet.PrintSetup.FitWidth = sSheet.PrintSetup.FitWidth;    //调整方式页宽
    //    dSheet.PrintSetup.FooterMargin = sSheet.PrintSetup.FooterMargin;
    //    dSheet.PrintSetup.HeaderMargin = sSheet.PrintSetup.HeaderMargin;
    //    //页边距
    //    dSheet.SetMargin(MarginType.TopMargin, sSheet.GetMargin(MarginType.TopMargin));
    //    dSheet.SetMargin(MarginType.BottomMargin, sSheet.GetMargin(MarginType.BottomMargin));
    //    dSheet.SetMargin(MarginType.LeftMargin, sSheet.GetMargin(MarginType.LeftMargin));
    //    dSheet.SetMargin(MarginType.RightMargin, sSheet.GetMargin(MarginType.RightMargin));
    //    dSheet.SetMargin(MarginType.HeaderMargin, sSheet.GetMargin(MarginType.HeaderMargin));
    //    dSheet.SetMargin(MarginType.FooterMargin, sSheet.GetMargin(MarginType.FooterMargin));
    //    //页眉页脚
    //    dSheet.Header.Left = sSheet.Header.Left;
    //    dSheet.Header.Center = sSheet.Header.Center;
    //    dSheet.Header.Right = sSheet.Header.Right;
    //    dSheet.Footer.Left = sSheet.Footer.Left;
    //    dSheet.Footer.Center = sSheet.Footer.Center;
    //    dSheet.Footer.Right = sSheet.Footer.Right;
    //    //工作表Sheet参数设置
    //    dSheet.IsPrintGridlines = sSheet.IsPrintGridlines;          //true: 打印整表网格线。不单独设置CellStyle时外框实线内框虚线。 false: 自己设置网格线
    //    dSheet.FitToPage = sSheet.FitToPage;                        //自适应页面
    //    dSheet.HorizontallyCenter = sSheet.HorizontallyCenter;      //打印页面为水平居中
    //    dSheet.VerticallyCenter = sSheet.VerticallyCenter;          //打印页面为垂直居中
    //    dSheet.RepeatingRows = sSheet.RepeatingRows;                //工作表顶端标题行范围
    //}

    #endregion Xls转Xlsx

    ///<summary>
    /// 将excel导入到datatable
    /// </summary>
    /// <param name="filePath">excel路径</param>
    /// <param name="isFirtRowHead">第一行是否是列名</param>
    /// <returns>返回datatable</returns>
    public static DataTable ExcelToDataTable(string filePath, bool isFirtRowHead)
    {
        DataTable dataTable = null;
        // FileStream fs = null;
        DataColumn column = null;
        DataRow dataRow = null;
        IWorkbook workbook = null;
        ISheet sheet = null;
        IRow row = null;
        ICell cell = null;
        int startRow = 0;
        try
        {
            // using (fs = File.OpenRead(filePath))
            FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            {
                // 2007版本
                if (filePath.IndexOf(".xlsx") > 0)
                    workbook = new XSSFWorkbook(fs);
                // 2003版本
                else if (filePath.IndexOf(".xls") > 0)
                    workbook = new HSSFWorkbook(fs);

                if (workbook != null)
                {
                    sheet = workbook.GetSheetAt(0); //读取第一个sheet，当然也可以循环读取每个sheet
                    dataTable = new DataTable();
                    if (sheet != null)
                    {
                        int rowCount = sheet.LastRowNum; //总行数
                        if (rowCount > 0)
                        {
                            IRow firstRow = sheet.GetRow(0); //第一行
                            int cellCount = firstRow.LastCellNum; //列数

                            #region 构建datatable的列

                            if (isFirtRowHead) //第一行是否是列名
                            {
                                dataTable.Columns.Add(new DataColumn("表格索引"));//从0开始计数

                                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                {
                                    cell = firstRow.GetCell(i);
                                    if (cell != null)
                                    {
                                        if (cell.StringCellValue != null)
                                        {
                                            column = new DataColumn(cell.StringCellValue);
                                            dataTable.Columns.Add(column);
                                        }
                                    }
                                }
                            }
                            else
                            {
                                dataTable.Columns.Add(new DataColumn("表格索引"));//从0开始计数
                                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                {
                                    column = new DataColumn("column" + (i + 1));
                                    dataTable.Columns.Add(column);
                                }
                            }

                            #endregion 构建datatable的列

                            // bool IsEmptyRow = true; //默认表示空行

                            if (isFirtRowHead) //第一行是否是列名
                                startRow = 1; //如果第一行是列名，则从第二行开始读取
                            //填充行
                            for (int rowIndex = startRow; rowIndex <= rowCount; ++rowIndex)
                            {
                                //IsEmptyRow = true;
                                row = sheet.GetRow(rowIndex);
                                if (row == null) continue;//获取的行为null 跳过
                                if (row.FirstCellNum < 0) continue;//
                                //datatable 新增一行
                                dataRow = dataTable.NewRow();

                                dataRow[0] = rowIndex;
                                for (int columnIndex = row.FirstCellNum; columnIndex < cellCount; ++columnIndex)
                                {
                                    var columnIndex2 = columnIndex + 1;//这里加1，是因为，默认第一列是表格索引列，后来定位是excle中哪一行的
                                    cell = row.GetCell(columnIndex);
                                    if (cell == null)
                                    {
                                        dataRow[columnIndex2] = "";
                                    }
                                    else
                                    {
                                        //获取cell值
                                        GetExcelCellValue(sheet, cell.CellType, row, cell, dataRow, columnIndex2);
                                    }

                                    //if (dataRow[m] != null && !string.IsNullOrWhiteSpace(dataRow[m].ToString()))
                                    //{
                                    //    IsEmptyRow = false; //表示不是空行
                                    //}
                                }
                                dataTable.Rows.Add(dataRow);
                                //if (IsEmptyRow == false)
                                //{
                                //    dataTable.Rows.Add(dataRow);
                                //}
                            }
                        }
                    }
                }
            }
            //return dataTable;
        }
        catch (Exception ex)
        {
            LogEx.Error(ex, "Excel\\Excel转DataTable");
        }

        return dataTable;
    }

    public static void GetExcelCellValue(ISheet sheet, CellType cellType, IRow row, ICell cell, DataRow dataRow, int columnIndex)
    {
        //CellType(Unknown = -1, Numeric = 0, String = 1, Formula = 2, Blank = 3, Boolean = 4, Error = 5,)
        switch (cellType)
        {
            case CellType.Blank:
                dataRow[columnIndex] = "";
                break;

            case CellType.Numeric:
                //short format = cell.CellStyle.DataFormat;
                ////对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                //if (format == 14 || format == 31 || format == 57 || format == 58)
                //    dataRow[m] = cell.DateCellValue;
                //else
                //    dataRow[m] = cell.NumericCellValue;

                //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                if (DateUtil.IsCellDateFormatted(cell)) //日期类型
                {
                    dataRow[columnIndex] = cell.DateCellValue;
                }
                else //其他数字类型
                {
                    dataRow[columnIndex] = cell.NumericCellValue;
                }

                break;

            case CellType.Formula: //公式类型
                {
                    //// 2007版本
                    //if (filePath.IndexOf(".xlsx") > 0)
                    //{
                    //    XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook);
                    //    dataRow[m] = eva.Evaluate(cell).StringValue;
                    //}
                    //// 2003版本
                    //else if (filePath.IndexOf(".xls") > 0)
                    //{
                    //    HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
                    //    dataRow[m] = eva.Evaluate(cell).StringValue;
                    //}

                    NPOI.SS.Formula.BaseFormulaEvaluator evaluator;
                    if (sheet is XSSFSheet)// 2007版本
                    {
                        evaluator = new XSSFFormulaEvaluator(sheet.Workbook);
                    }
                    else
                    {// 2003版本
                        evaluator = new HSSFFormulaEvaluator(sheet.Workbook);
                    }

                    var formulaValue = evaluator.Evaluate(cell);

                    //递归调用
                    GetExcelCellValue(sheet, formulaValue.CellType, row, cell, dataRow, columnIndex);
                    //if (formulaValue.CellType == CellType.Numeric)
                    //{
                    //    dataRow[columnIndex] = formulaValue.NumberValue;
                    //}
                    //else if (formulaValue.CellType == CellType.String)
                    //{
                    //    dataRow[columnIndex] = formulaValue.StringValue;
                    //}

                    /*
                     * https://www.cnblogs.com/zc-bao/p/15476569.html
                     BaseFormulaEvaluator evaluator;
if (sheet is XSSFSheet)
{
evaluator = new XSSFFormulaEvaluator(sheet.Workbook);
}
else
{
evaluator = new HSSFFormulaEvaluator(sheet.Workbook);
}

var formulaValue = evaluator.Evaluate(cell);
if (formulaValue.CellType == CellType.Numeric)
{
return formulaValue.NumberValue;
}
else if (formulaValue.CellType == CellType.String)
{
return formulaValue.StringValue;
}
                     */

                    break;
                }
            case CellType.Boolean:
                // Boolean type
                dataRow[columnIndex] = cell.BooleanCellValue.ToString();
                break;

            case CellType.Error:
                dataRow[columnIndex] = row.GetCell(columnIndex).ErrorCellValue.ToString();
                break;

            case CellType.String:
                dataRow[columnIndex] = cell.StringCellValue;
                break;
        }
    }

    ///<summary>
    /// 获取excel列名
    /// </summary>
    /// <param name="filePath">excel路径</param>
    /// <returns>返回datatable</returns>
    public static List<string> GetExcelColumns(string filePath)
    {
        var columns = new List<string>();

        IWorkbook workbook = null;
        ISheet sheet = null;
        IRow row = null;
        ICell cell = null;
        try
        {
            // using (fs = File.OpenRead(filePath))
            FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            {
                // 2007版本
                if (filePath.IndexOf(".xlsx") > 0)
                    workbook = new XSSFWorkbook(fs);
                // 2003版本
                else if (filePath.IndexOf(".xls") > 0)
                    workbook = new HSSFWorkbook(fs);

                if (workbook != null)
                {
                    sheet = workbook.GetSheetAt(0); //读取第一个sheet，当然也可以循环读取每个sheet
                    if (sheet != null)
                    {
                        int rowCount = sheet.LastRowNum; //总行数
                        if (rowCount > 0)
                        {
                            IRow firstRow = sheet.GetRow(0); //第一行
                            int cellCount = firstRow.LastCellNum; //列数

                            //构建datatable的列

                            //从0开始计数
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                cell = firstRow.GetCell(i);
                                if (!string.IsNullOrWhiteSpace(cell?.StringCellValue))
                                {
                                    columns.Add(cell?.StringCellValue.Trim());
                                }
                            }
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            LogEx.Error(ex, "Excel\\Excel转DataTable");
        }
        return columns;
    }

    /// <summary>
    /// 获取DataTable 行中某列的值
    /// </summary>
    /// <param name="datarow"></param>
    /// <param name="columnname"></param>
    /// <returns></returns>
    public static string GetDataRowValue(DataRow datarow, string columnname)
    {
        try
        {
            if (string.IsNullOrWhiteSpace(columnname)) return "";

            var data = datarow[columnname.ToLower()]?.ToString() ?? "";
            return data;
        }
        catch (Exception e)
        {
        }

        return "";
    }

    /// <summary>
    /// 获取DataTable 行索引
    /// </summary>
    /// <param name="datarow"></param>
    /// <param name="columnname"></param>
    /// <returns></returns>
    public static int GetDataRowIndex(DataRow datarow, string columnname)
    {
        var data = GetDataRowValue(datarow, columnname);

        var state = int.TryParse(data, out var rowIndex);
        if (state) return rowIndex;
        return 0;
    }
}